package com.yuanqi.dz.dc.adapters;

import com.yuanqi.dz.dc.util.DZConfigRead;
import com.yuanqitech.powermop.dz.DZInfo;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import pnbclient.helper.StringHelper;

public class ReadExcel
{
  private static int START_ROW = 20;

  public ReadExcel() {
    START_ROW = Integer.valueOf(DZConfigRead.getProperties("start_row")).intValue();
  }

  public List<DZInfo> readXlsx(String path) throws IOException
  {
    InputStream is = new FileInputStream(path);
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);

    List list = new ArrayList();

    for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
      XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
      if (xssfSheet != null)
      {
        DZInfo dzInfo = null;

        for (int rowNum = START_ROW; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
          XSSFRow xssfRow = xssfSheet.getRow(rowNum);
          if (xssfRow != null) {
            dzInfo = new DZInfo();
            XSSFCell tramid = xssfRow.getCell(3);
            XSSFCell bankname = xssfRow.getCell(5);
            XSSFCell cardnostart = xssfRow.getCell(7);
            XSSFCell cardnoend = xssfRow.getCell(8);
            XSSFCell tran_date = xssfRow.getCell(9);
            XSSFCell tran_time = xssfRow.getCell(10);
            XSSFCell tran_code = xssfRow.getCell(11);
            XSSFCell refer = xssfRow.getCell(25);

            String trancode = getValue(tran_code);
            if ("消费".equals(trancode))
              trancode = "S1";
            else if ("消费撤销".equals(trancode))
              trancode = "S2";
            else if ("联机退货".equals(trancode)) {
              trancode = "S3";
            }
            XSSFCell auth_code = xssfRow.getCell(12);
            XSSFCell amount = xssfRow.getCell(13);

            dzInfo.setTermId(getValue(tramid));
            dzInfo.setBankName(getValue(bankname));
            dzInfo.setTranCardNum(getValue(cardnostart) + getValue(cardnoend));
            dzInfo.setTrDateTime(getValue(tran_date) + getValue(tran_time));
            dzInfo.setTranCode(trancode);
            dzInfo.setAuthCode(getValue(auth_code));
            dzInfo.setAmount(Float.valueOf(getValue(amount)));
            dzInfo.setReferNum(getValue(refer));
            list.add(dzInfo);
          }
        }
      }
    }

    return list;
  }

  public static void main(String[] args) throws IOException {
    String path = "D:\\DZ\\SHOP.105121000001424.20170113.02.success.xls";
    new ReadExcel().readXls(path);
  }

  public List<DZInfo> readXls(String path)
    throws IOException
  {
    InputStream is = new FileInputStream(path);
    HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);

    List list = new ArrayList();

    for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
      HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
      if (hssfSheet != null)
      {
        DZInfo dzInfo = null;

        for (int rowNum = START_ROW; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
          HSSFRow hssfRow = hssfSheet.getRow(rowNum);
          if (hssfRow != null)
          {
            dzInfo = new DZInfo();
            HSSFCell refer = hssfRow.getCell(24);
            String refernum = getValue(refer);
            if ((refer != null) && (!StringHelper.isEmpty(refernum)))
            {
              Boolean isNumFlag = Boolean.valueOf(isNumeric(refernum));
              if (isNumFlag.booleanValue())
              {
                HSSFCell tramid = hssfRow.getCell(2);
                HSSFCell bankname = hssfRow.getCell(3);
                HSSFCell cardno = hssfRow.getCell(6);
                String trancardnum = getValue(cardno);
                if ((trancardnum != null) && (trancardnum.contains("-"))) {
                  trancardnum = trancardnum.split("-")[0];
                }
                HSSFCell tran_date = hssfRow.getCell(8);
                HSSFCell tran_time = hssfRow.getCell(9);
                HSSFCell tran_code = hssfRow.getCell(10);
                String trancode = getValue(tran_code);
                if ("消费".equals(trancode)) trancode = "S1";
                else if ("消费撤销".equals(trancode)) trancode = "S2";
                else if ("联机退货".equals(trancode)) trancode = "S3";
                HSSFCell auth_code = hssfRow.getCell(11);
                HSSFCell amount = hssfRow.getCell(12);

                dzInfo.setTermId(getValue(tramid));
                dzInfo.setBankName(getValue(bankname));
                dzInfo.setTranCardNum(trancardnum);
                dzInfo.setTrDateTime(getValue(tran_date) + " " + getValue(tran_time));
                dzInfo.setTranCode(trancode);
                dzInfo.setAuthCode(getValue(auth_code));
                dzInfo.setAmount(Float.valueOf(getValue(amount)));
                dzInfo.setReferNum(getValue(refer));
                list.add(dzInfo);
              }
            }
          }
        }
      }
    }
    return list;  //1519833600000
  }
  
  
  
  
  public List<DZInfo> readXls2(String path)
		    throws IOException
		  {
		    InputStream is = new FileInputStream(path);
		    HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);

		    List list = new ArrayList();

		    for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
		      HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
		      if (hssfSheet != null)
		      {
		        DZInfo dzInfo = null;

		        for (int rowNum = START_ROW; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
		          HSSFRow hssfRow = hssfSheet.getRow(rowNum);
		          if (hssfRow != null)
		          {
		            dzInfo = new DZInfo();
		            HSSFCell refer = hssfRow.getCell(25);
		            String refernum = getValue(refer);
		            if ((refer != null) && (!StringHelper.isEmpty(refernum)))
		            {
		              Boolean isNumFlag = Boolean.valueOf(isNumeric(refernum));
		              if (isNumFlag.booleanValue())
		              {
		                HSSFCell tramid = hssfRow.getCell(2);
		                HSSFCell bankname = hssfRow.getCell(3);
		                HSSFCell cardno = hssfRow.getCell(6);
		                String trancardnum = getValue(cardno);
		                if ((trancardnum != null) && (trancardnum.contains("-"))) {
		                  trancardnum = trancardnum.split("-")[0];
		                }
		                HSSFCell tran_date = hssfRow.getCell(8);
		                HSSFCell tran_time = hssfRow.getCell(9);
		                HSSFCell tran_code = hssfRow.getCell(10);
		                String trancode = getValue(tran_code);
		                if ("消费".equals(trancode)) trancode = "S1";
		                else if ("消费撤销".equals(trancode)) trancode = "S2";
		                else if ("联机退货".equals(trancode)) trancode = "S3";
		                HSSFCell auth_code = hssfRow.getCell(11);
		                HSSFCell amount = hssfRow.getCell(12);

		                dzInfo.setTermId(getValue(tramid));
		                dzInfo.setBankName(getValue(bankname));
		                dzInfo.setTranCardNum(trancardnum);
		                dzInfo.setTrDateTime(getValue(tran_date) + " " + getValue(tran_time));
		                dzInfo.setTranCode(trancode);
		                dzInfo.setAuthCode(getValue(auth_code));
		                dzInfo.setAmount(Float.valueOf(getValue(amount)));
		                dzInfo.setReferNum(getValue(refer));
		                list.add(dzInfo);
		              }
		            }
		          }
		        }
		      }
		    }
		    return list;
		  }

  private String getValue(XSSFCell xssfRow)
  {
    if (xssfRow.getCellType() == 4)
      return String.valueOf(xssfRow.getBooleanCellValue());
    if (xssfRow.getCellType() == 0) {
      return String.valueOf(xssfRow.getNumericCellValue());
    }
    return String.valueOf(xssfRow.getStringCellValue());
  }

  private String getValue(HSSFCell hssfCell)
  {
    if (hssfCell == null) {
      return "";
    }
    if (hssfCell.getCellType() == 4)
      return String.valueOf(hssfCell.getBooleanCellValue());
    if (hssfCell.getCellType() == 0) {
      return String.valueOf(hssfCell.getNumericCellValue());
    }
    return String.valueOf(hssfCell.getStringCellValue());
  }

  public static boolean isNumeric(String str)
  {
    for (int i = 0; i < str.length(); i++)
    {
      if (!Character.isDigit(str.charAt(i))) {
        return false;
      }
    }
    return true;
  }
}